-- @owner: @haomeng802
-- @date: 2023/1/3
-- @testpoint: 范围分区表结合connect by子句

--step1:创建范围分区表，插入数据  expect:建表成功，插入数据成功
drop table if exists t_connectby_004 cascade;
create table t_connectby_004(e_id int,e_name varchar(10),l_id int)
partition by range(e_id)
(partition p1 values less than(10),
 partition p2 values less than(20),
 partition p3 values less than(30),
 partition p4 values less than(maxvalue));

insert into t_connectby_004
select a,c,case when b < 0 then 0 else b end b
from (select generate_series(1, 30) a,'asd_'||generate_series(1,30) c,generate_series(-2, 4) b);

--step2:查询以l_id为0开始的节点的直属节点  expect:成功
select * from t_connectby_004 start with l_id = 0 connect by nocycle prior l_id = e_id limit 20;

--step3:结合层次递归查询函数查看递归层级  expect:成功
select sys_connect_by_path(e_name,'-') as path, *, level from t_connectby_004 start with l_id = 0 connect by prior l_id = e_id limit 20;

--step4:清理环境   expect:成功
drop table t_connectby_004 cascade;
